package com.hzdl.library.dao.impl;

import com.hzdl.library.dao.UserDao;
import com.hzdl.library.domain.BookInfo;
import com.hzdl.library.domain.LendList;
import com.hzdl.library.domain.ReaderCard;
import com.hzdl.library.domain.ReaderInfo;
import com.hzdl.library.util.JDBCUtils;
import org.junit.Test;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.Date;
import java.util.List;

//姚奇


public class UserDaoImpl implements UserDao {
    JdbcTemplate jdbcTemplate=new JdbcTemplate(JDBCUtils.getDataSource());



    //借书
    @Override
    public Integer lend(String name, Integer readerId) {
        //判断读者id输对没有
        String sql0="select * from reader_card where reader_id=?";
        try {
           jdbcTemplate.queryForObject(sql0, new BeanPropertyRowMapper<ReaderCard>(ReaderCard.class), readerId);
        } catch (DataAccessException e) {
            return 0;
        }


        //查询书籍状态
        String sql="select * from book_info where name=?";
        BookInfo bookInfo1 = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<BookInfo>(BookInfo.class), name);
        Integer integer = bookInfo1.getState();

        //书籍未归还，不能借书
        if(integer==0) {
            return integer;
        }
        //书籍已归还，可以借书
        //修改书状态
        String sql1="update book_info set state =0 where name=?";
        jdbcTemplate.update(sql1,name);
        //查询book_id
        String sql2="select book_id from book_info where name=?";
        BookInfo bookInfo = jdbcTemplate.queryForObject(sql2, new BeanPropertyRowMapper<BookInfo>(BookInfo.class), name);
        Integer book_id = bookInfo.getBook_id();
        //添加借书信息
        String sql3="insert into lend_list values(null,?,?,?,null)";
        jdbcTemplate.update(sql3,book_id,readerId,new Date());

        return integer;
    }

    //还书
    @Override
    public Integer returnBook(Integer book_id) {

        //查询书籍状态
        String sql="select state from book_info where book_id=?";
        Integer integer = jdbcTemplate.queryForObject(sql, Integer.class, book_id);
        //书籍已归还
        if(integer==1) {
            return integer;
        }
        //书籍未归还，还书
        //填写还书日期
        String sql1="update lend_list set back_date=ifnull(back_date,?) where book_id=?";
        jdbcTemplate.update(sql1,new Date(),book_id);

        //修改书籍状态
        String sql2="update book_info set state = 1 where book_id=?";
        jdbcTemplate.update(sql2,book_id);

        return integer;
    }

    //查询书籍借还日志
    @Override
    public List<LendList> lendRecord(String text, Integer index, int rows) {
        String sql="select * from lend_list where book_id like concat('%',?,'%') order by sernum desc limit ?,? ";
        List<LendList> list=null;
        if(text.equals(""))
        {
            list= jdbcTemplate.query(sql, new BeanPropertyRowMapper<LendList>(LendList.class), text, index, rows);
        }else{
            Integer book_id=Integer.parseInt(text);
            list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<LendList>(LendList.class), book_id, index, rows);
        }
        return list;
    }

    @Override
    public Integer judge(String name) {
        //查询书籍状态
        String sql="select state from book_info where name=?";
        Integer state = jdbcTemplate.queryForObject(sql, Integer.class, name);
        //state=1可以外借state=0,不能外借可以归还
        return state;

    }

    //查询借阅日志总条数
    @Override
    public Integer findCount(String text) {
        String sql="select count(*) from lend_list where book_id like concat('%',?,'%')";
        Integer integer=null;
        //如果是“”，查所有
        if(text.equals(""))
        {
            integer = jdbcTemplate.queryForObject(sql, Integer.class,text);
            return integer;
        }
        //如果不是，转换为int，模糊查询总条数
        int i = Integer.parseInt(text);
        integer = jdbcTemplate.queryForObject(sql, Integer.class,i);
        return integer;


    }

    //删除一条日志信息
    @Override
    public void deleteOne(int sernum) {
        String sql="delete from lend_list where sernum=?";
        jdbcTemplate.update(sql,sernum);
    }

    //查询个人日志
    @Override
    public List<LendList> findLog(Integer reader_id) {
        String sql="select * from lend_list where reader_id=? order by sernum desc";
        List<LendList> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<LendList>(LendList.class), reader_id);
        return list;
    }

    //用户信息修改
    @Override
    public void updateReader(ReaderInfo readerInfo) {

        String sql = "update reader_info set name = ?,sex = ?,birth = ?,address = ?, telcode = ? where reader_id= ?";
        jdbcTemplate.update(sql,readerInfo.getName(),readerInfo.getSex(),readerInfo.getBirth(),readerInfo.getAddress(),readerInfo.getTelcode(),readerInfo.getReader_id());
    }

    @Override
    public List<ReaderInfo> selectAllReader() {

        String sql = "select * from reader_info";

        List<ReaderInfo> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<ReaderInfo>(ReaderInfo.class));

        return list;
    }

    //删除用户
    @Override
    public void deleteReader(String readerId) {
        String sql = "delete from reader_info where reader_id = ?";

        jdbcTemplate.update(sql,readerId);
    }

    //导出借还日志
    @Override
    public List<LendList> downLoad() {
        String sql="select * from lend_list";
        List<LendList> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<LendList>(LendList.class));
        return list;
    }

    //测试借书
    @Test
    public void method()
    {
       // lend("人类简史",1501014102);
        Integer integer = lend("大雪中的山庄",50000010);
        System.out.println(integer);

    }
}
